package com.sduept.nwld.dataserver.manager.countanalyse;

import com.sduept.core.dao.SuperDAO;
import com.sduept.nwld.dataserver.model.countanalyse.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.DecimalFormat;
import java.util.*;


@Service
public class ProtectAnalysisManger {
	@Autowired
	private SuperDAO dao;
	@Autowired
	private ProtectKpiAnalysisManger manager;

	public String getCheckPlans(String year) {
		String sql = "select year,sub_company,sum(plan_value) plan,sum(finish_value) reality from t_count_protect_check_plan where year = "+year+" group  by year,sub_company";
		String sql2 = "select distinct  sub_company  from t_count_protect_check_plan";//一共有多少个分、子公司
		List<?>list2 = dao.nativeQuery(sql2);
		List<?> list = dao.nativeQuery(sql);
		StringBuffer buff = new StringBuffer();
		buff.append("[");
		int j = 0;
		for (int i = 0 ; i < list.size() ; i++) {
			Object[] objs = (Object[]) list.get(i);
			String name = objs[1].toString();
			String plan = objs[2].toString();
			String reality = objs[3].toString();
			CheckPlan c = new CheckPlan();
			 c.setMonth(0);
			 c.setPlan(new Float(plan));
			 c.setReality(new Float(reality));
			 c.setSubCompany(name);
			 c.setYear(new Integer(year));
			dao.create(c, CheckPlan.class);
			if(j == 0){
				buff.append("{name:"+year+",value:[");
			}
			if ( j < list2.size()) {
				buff.append("{name:'"+name+"',value:[{name:'计划数',value:"+plan+"},{name:'实际完成数',value:"+reality+"}]},");
			}
			if(j == list2.size()-1){
				buff.append("]},");
				j = -1;
			}
			j++;
		}
		buff.append("]");
		return buff.toString();
	}

	public String getMonthCheckPlans(String year,String month) {
		String jsonString = "";
		 StringBuffer buff = new StringBuffer();
		 buff.append("[");
		 if(month.equals("全年")){
			 month = "0";
		 }
		 Integer y = Integer.parseInt(year);
		 Integer m = Integer.parseInt(month);
//		 List<CheckPlan> checks = dao.findByCondition("year = "+year+" and month = "+month, CheckPlan.class);
		 List<CheckPlan> checks = dao.findByCondition(CheckPlan.class, "year = ? and month = ?", y,m);
		 if(month == "0" && checks.size() ==0 ){
			 return getCheckPlans(year);
		 }
		 buff.append("{'name':'"+month+"月','value':[");
		 for (CheckPlan c : checks) {
			 buff.append("{'name':'"+c.getSubCompany()+"','value':[{'name':'计划数','value':"+c.getPlan()+"},{'name':'实际完成数','value':"+c.getReality()+"}]},");
		}
		 buff.append("]}");
		 buff.append("]");
		 jsonString = buff.toString();
		return jsonString;
	}
	
	public String getCompanyCounterMeasureDatas() {
		List<?> companys = dao.nativeQuery("select distinct sub_company from t_count_protect_counter_measure_sub_statistics");
		StringBuffer buff = new StringBuffer();
		buff.append("[");
		for (Object company : companys) {
			List<ProtectCounterMeasureSub> protectCounterMeasureSub = dao.findByCondition(ProtectCounterMeasureSub.class, "year=0 and company=?", company);
			int finish = 0;
			int amount = 0;
			for (ProtectCounterMeasureSub p : protectCounterMeasureSub) {
				finish = finish+p.getOntimeAmount();
				amount = amount+p.getAmount();
			}
			if(amount == 0 ) {
				buff.append("{company:'"+company+"',ratio:0},");
			} else {
				buff.append("{company:'"+company+"',ratio:"+finish * 100/amount+"},");
			}
		}
		buff.append("]");
		return buff.toString();
	}
	
	public String getFaultDatasByGroupYearRankType() {
		List<ProtectCounterMeasure> lp = dao.findAll(ProtectCounterMeasure.class);
		StringBuffer buff = new StringBuffer();
		buff.append("[");
		for (ProtectCounterMeasure protectCounterMeasure : lp) {
			buff.append("{'name':'"+protectCounterMeasure.getName()+"','value':[");
			List<ProtectCounterMeasureSub> lsub = dao.findByPid(protectCounterMeasure.getId(), ProtectCounterMeasureSub.class);
			List<?> companys = dao.nativeQuery("select distinct sub_company from t_count_protect_counter_measure_sub_statistics where pid = '"+protectCounterMeasure.getId()+"'");
			for (Object company : companys) {
				int acount = 0;
				int ontimeAcount = 0;
				int year = 0;
				Map<Integer, Integer> map = new HashMap<>();
				for (ProtectCounterMeasureSub sub : lsub) {
					if(sub.getCompany().equals(company)) {
						year = sub.getYear();
						if(year == 0) {
							acount = sub.getAmount();
							ontimeAcount = sub.getOntimeAmount();
						}
						if(!map.containsKey(year) && year != 0){
							map.put(year, sub.getOntimeAmount());
						}
					}
				}
				buff.append("{'name':'"+company+"','value':[{'name':'应反措','value':"+acount+"},{'name':'已完成','value':"+ontimeAcount+"},");
				Set<Integer> set = map.keySet();
				for (Integer integer : set) {
					buff.append("{'name':'至"+integer+"年底完成数','value':"+map.get(integer)+"},");
				}
				buff.append("]},");
			}
			buff.append("]},");
		}
		buff.append("]");
		return buff.toString();
	}

	public String getProtectCounterMeasureName(String pid) {
		List<ProtectCounterMeasure> protectCounterMeasures = dao.findByCondition(ProtectCounterMeasure.class, "id=?", pid);
		ProtectCounterMeasure protectCounterMeasure = protectCounterMeasures.get(0);
		return protectCounterMeasure.getName();
	}
	
	public String getAllCounterMeasures() {
		List<String> lp = (List<String>) dao.nativeQuery("select distinct pid from t_count_protect_counter_measure_sub_statistics");
		StringBuffer buff = new StringBuffer();
		buff.append("[");
		for(int i=0; i<lp.size(); i++) {
			String id = lp.get(i);
			String name = getProtectCounterMeasureName(id);
			List<?> ontimeAmount = dao.nativeQuery("select sum(ontime_amount) from t_count_protect_counter_measure_sub_statistics where pid = '"+id+"' and year =0");
	    	List<?> overdueAmount = dao.nativeQuery("select sum(overdue_amount) from t_count_protect_counter_measure_sub_statistics where pid = '"+id+"'and year=0");
	    	List<?> amount = dao.nativeQuery("select sum(amount) from t_count_protect_counter_measure_sub_statistics where pid = '"+id+"'and year=0");
	    	long onAmount = new Long(ontimeAmount.get(0).toString());
	    	long overAmount = new Long(overdueAmount.get(0).toString());
	    	long finishAmount = onAmount + overAmount;
	    	long amountSum = new Long(amount.get(0).toString());
	    	int ratio = (int) (finishAmount*100/amountSum);
	    	buff.append("{order:"+(i+1)+",name:'"+name+"',finishRatio:"+ratio+"},");
		}
		buff.append("]");
		return buff.toString();
	}
	
	/**
	 * 获得某一年的保护缺陷
	 * */
	public List<CountProtectDefect> getCountProtectDefectByYear(String year){
//		String whereClause="date_format(occur_date, '%Y') = '"+year+"'";
//		List<CountProtectDefect> result=dao.findByCondition(whereClause, CountProtectDefect.class);
		List<CountProtectDefect> result=dao.findByCondition(CountProtectDefect.class, "date_format(occur_date, '%Y') = ?", year);
		return result;
	}
	/**
	 * 按缺陷类型分类(保护装置，二次回路，保护通道)
	 * */
	public Map<String,List<CountProtectDefect>> getCountProtectDefectByType(List<CountProtectDefect> pdList){
		Map<String,List<CountProtectDefect>> map=new HashMap<>();
		List<CountProtectDefect> deviceList=new ArrayList<>();
		List<CountProtectDefect> hlList=new ArrayList<>();
		List<CountProtectDefect> channelList=new ArrayList<>();
		for(CountProtectDefect pd:pdList){
			if("装置".equals(pd.getDefectType())){
				deviceList.add(pd);
			}
			if("二次回路".equals(pd.getDefectType())){
				hlList.add(pd);
			}
			if("通道".equals(pd.getDefectType())){
				channelList.add(pd);
			}
		}
		map.put("保护装置", deviceList);
		map.put("二次回路", hlList);
		map.put("保护通道", channelList);
		return map;
	}
	
	public List<?> getElectricityFaultByYear(String year){
		 String sql = "select  fault_type,area,count(*) from t_count_electricity_generator_fault  where fault_time >= '"+year+"-01-01 00:00:00' and fault_time <='"+year+"-12-31 11:59:59' group by fault_type,area";
		 List<?> list = dao.nativeQuery(sql);
		 return list;
	}

	public List<?> getTransmissionFaultByYear(String year) {
		String sql = "select  fault_type,area,count(*) from t_count_transformer_fault  where fault_time >= '"+year+"-01-01 00:00:00' and fault_time <='"+year+"-12-31 11:59:59' group by fault_type,area";
		List<?> list = dao.nativeQuery(sql);
		 return list;
	}
	public List<?> getBusbarFaultByYear(String year) {
		String sql = "select  fault_type,area,count(*) from t_count_busbar_fault  where fault_time >= '"+year+"-01-01 00:00:00' and fault_time <='"+year+"-12-31 11:59:59' group by fault_type,area";
		List<?> list = dao.nativeQuery(sql);
		 return list;
	}
	/**
	 * 根据年份获取开关上设备异常信息
	 * */
	public List<BreakerException> getBreakerExceptionByYear(String year){
//		String whereClause="date_format(fault_time, '%Y') = '"+year+"'";
//		List<BreakerException> result=dao.findByCondition(whereClause, BreakerException.class);
		List<BreakerException> result=dao.findByCondition(BreakerException.class, "date_format(fault_time, '%Y') = ?", year);
		return result;
	}
	
	public List<BreakerException> getAllBreakerException(){
		List<BreakerException> result = dao.findAll(BreakerException.class);
		return result;
	}

	public List<?> getLineFaultTypeDataByYear(int year) {
		String sql = "select  faultType,rank,count(*) from t_cim_fault  where etype='线路' and status=0 and faultTime >= '"+year+"-01-01 00:00:00' and faultTime <='"+year+"-12-31 23:59:59' and rank is not null group by faultType,rank";
		List<?> list = dao.nativeQuery(sql);
		return list;
	}

	public List<String> getColumns(String columnName) {
		String sql = "select distinct "+columnName+"  from t_cim_fault where  " +columnName+" is not null and etype='线路' and status=0 ";
		List<?> list =  dao.nativeQuery(sql);
		List<String> s = new ArrayList<String>();
		for (Object object : list) {
			s.add(object.toString());
		}
		 return s;
	}
	public long getCountsLineFaultByCondition(String year, int mouth, String condition) {
		String sql = "where  etype='线路' and status=0 and faultTime >= '"+year+"-"+mouth+"-01 00:00:00' and faultTime <='"+year+"-"+mouth+"-31 23:59:59'"+condition;
		return  dao.count("t_cim_fault", "*",sql);
	}
	/**
	 * 上月保护正确动作率  
	 * @param currentYear
	 * @param i 为0查找全年
	 * @return 保留两位小数
	 */
	public Float getActRealityAverage(Integer year, Integer month,Integer voltage, int unitType) {
		List<ProtectCorrectAct> list = manager.getSummaryOfCorrectActsBySubCompany(year,month,voltage,unitType);
		Integer actNum = 0;
		Integer unwantedActNum = 0;
		for(ProtectCorrectAct p:list) {
			if(p.getActNum() != null) {
				actNum += p.getActNum();
			}
			if(p.getUnwantedActNum() != null) {
				unwantedActNum += p.getUnwantedActNum();
			}
		}
		float ratio = 0;
		if(actNum == 0) {
			ratio = 100f;
		}else {
			ratio = 100f*(1-(float)unwantedActNum/actNum);
		}
		DecimalFormat df = new DecimalFormat("0.00");
		ratio = new Float(df.format(ratio));
		return ratio>100?100:ratio;
	}
	/**
	 * 故障快速切除率  
	 * @param currentYear
	 * @param i  为0 查找全年
	 * @return 保留两位小数
	 */
	public Float getRemovalRealityAverage(Integer year, Integer month,Integer voltage, int unitType) {
		List<FaultFastRemove> list = manager.getSummaryOfFaultFastRemoveBySubCompany(year,month,voltage,unitType);
		Integer actNum = 0;
		Integer wantedActNum = 0;
		for(FaultFastRemove p:list) {
			if(p.getActNum() != null) {
				actNum += p.getActNum();
			}
			if(p.getWantedActNum() != null) {
				wantedActNum += p.getWantedActNum();
			}
		}
		float ratio = 0;
		if(actNum == 0) {
			ratio = 100f;
		}else {
			ratio = 100f*((float)wantedActNum/actNum);
		}
		DecimalFormat df = new DecimalFormat("0.00");
		ratio = new Float(df.format(ratio));
		return ratio>100?100:ratio;
	}
	/**
	 * 基于子公司的保护动作正确率
	 * @param year
	 * @param month 为0查找全年
	 * @return 返回值中的百分比均保留两位小数
	 */
	public List<ProtectCorrectAct> getCorrectActsByYear(int year, int month) {
		Integer m = null;
		if(month != 0) {
			m = month;
		}
		List<ProtectCorrectAct> list = manager.getSummaryOfCorrectActsBySubCompany(year, m, null);
		DecimalFormat df = new DecimalFormat("0.00");
		for (ProtectCorrectAct act : list) {
			act.setPlan(new Float(df.format(act.getPlan())));
			act.setReality(new Float(df.format(act.getReality())));
		}
		return list;
	}
	/**
	 * 基于子公司的故障快速切除率
	 * @param year
	 * @param month 为0查找全年
	 * @return 返回值中的百分比均保留两位小数
	 */
	public List<FaultFastRemove> getFaultFastRemoveByYear(int year, int month) {
		Integer m = null;
		if(month != 0) {
			m = month;
		}
		List<FaultFastRemove> list = manager.getSummaryOfFaultFastRemoveBySubCompany(year, m, null);
		DecimalFormat df = new DecimalFormat("0.00");
		for (FaultFastRemove act : list) {
			act.setPlan(new Float(df.format(act.getPlan())));
			act.setReality(new Float(df.format(act.getReality())));
		}
		return list;
	}
}
